// 管理员路由
const express = require('express');
const router = express.Router();
const { query, transaction } = require('../config/database');
const { authenticateToken, requireAdmin } = require('../middleware/auth');
const { validateId, validatePagination } = require('../middleware/validation');
const { calculatePagination, formatAmount } = require('../utils/helpers');
const ResponseHelper = require('../utils/response');

// 获取用户列表（管理员）
router.get('/users', authenticateToken, requireAdmin, validatePagination, async (req, res) => {
  try {
    const { page, pageSize, offset, limit } = calculatePagination(req.query.page, req.query.pageSize);
    const { role, status, search } = req.query;

    // 构建查询条件
    let whereClause = 'WHERE 1=1';
    let queryParams = [];

    if (role) {
      whereClause += ' AND role = ?';
      queryParams.push(role);
    }

    if (status) {
      whereClause += ' AND status = ?';
      queryParams.push(status);
    }

    if (search) {
      whereClause += ' AND (username LIKE ? OR phone LIKE ? OR real_name LIKE ?)';
      const searchPattern = `%${search}%`;
      queryParams.push(searchPattern, searchPattern, searchPattern);
    }

    // 获取用户总数
    const countResult = await query(
      `SELECT COUNT(*) as total FROM users ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    // 获取用户列表
    const users = await query(
      `SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at, updated_at 
       FROM users ${whereClause} 
       ORDER BY created_at DESC 
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    ResponseHelper.paginated(res, users, total, page, pageSize, '获取用户列表成功');

  } catch (error) {
    console.error('获取用户列表错误:', error);
    ResponseHelper.serverError(res, '获取用户列表失败');
  }
});

// 获取用户统计信息
router.get('/users/stats', authenticateToken, requireAdmin, async (req, res) => {
  try {
    // 获取用户总数和活跃用户数
    const [userCounts] = await query(`
      SELECT 
        COUNT(*) as totalUsers,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as activeUsers,
        SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as adminUsers,
        SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as newUsersToday
      FROM users
    `);

    // 获取用户注册趋势（最近30天）
    const registrationTrend = await query(`
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as count
      FROM users 
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
      GROUP BY DATE(created_at)
      ORDER BY date
    `);

    // 获取用户余额分布
    const balanceDistribution = await query(`
      SELECT
        CASE
          WHEN balance = 0 THEN '0元'
          WHEN balance > 0 AND balance <= 100 THEN '1-100元'
          WHEN balance > 100 AND balance <= 500 THEN '101-500元'
          WHEN balance > 500 THEN '500元以上'
        END as \`range\`,
        COUNT(*) as count
      FROM users
      GROUP BY \`range\`
    `);

    ResponseHelper.success(res, {
      ...userCounts,
      registrationTrend,
      balanceDistribution
    }, '获取用户统计成功');

  } catch (error) {
    console.error('获取用户统计错误:', error);
    ResponseHelper.serverError(res, '获取用户统计失败');
  }
});

// 更新用户信息
router.put('/users/:id', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const userId = req.params.id;
    const { username, email, real_name, role, status } = req.body;

    // 检查用户是否存在
    const existingUsers = await query('SELECT id FROM users WHERE id = ?', [userId]);
    if (existingUsers.length === 0) {
      return ResponseHelper.notFound(res, '用户不存在');
    }

    // 检查用户名是否已被其他用户使用
    if (username) {
      const duplicateUsers = await query(
        'SELECT id FROM users WHERE username = ? AND id != ?',
        [username, userId]
      );

      if (duplicateUsers.length > 0) {
        return ResponseHelper.error(res, '用户名已被使用', 400);
      }
    }

    // 更新用户信息
    await query(
      `UPDATE users SET 
       username = COALESCE(?, username),
       email = COALESCE(?, email),
       real_name = COALESCE(?, real_name),
       role = COALESCE(?, role),
       status = COALESCE(?, status),
       updated_at = NOW()
       WHERE id = ?`,
      [username, email, real_name, role, status, userId]
    );

    // 获取更新后的用户信息
    const updatedUsers = await query(
      'SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at, updated_at FROM users WHERE id = ?',
      [userId]
    );

    ResponseHelper.success(res, updatedUsers[0], '用户信息更新成功');

  } catch (error) {
    console.error('更新用户信息错误:', error);
    ResponseHelper.serverError(res, '更新用户信息失败');
  }
});

// 更新用户余额
router.put('/users/:id/balance', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const userId = req.params.id;
    const { amount, operation, remark } = req.body; // operation: 'add' | 'subtract' | 'set'

    if (!amount || amount <= 0) {
      return ResponseHelper.error(res, '金额必须大于0', 400);
    }

    if (!['add', 'subtract', 'set'].includes(operation)) {
      return ResponseHelper.error(res, '操作类型无效', 400);
    }

    // 检查用户是否存在
    const users = await query('SELECT id, balance FROM users WHERE id = ?', [userId]);
    if (users.length === 0) {
      return ResponseHelper.notFound(res, '用户不存在');
    }

    const currentBalance = parseFloat(users[0].balance);
    let newBalance;

    switch (operation) {
      case 'add':
        newBalance = currentBalance + parseFloat(amount);
        break;
      case 'subtract':
        newBalance = Math.max(0, currentBalance - parseFloat(amount));
        break;
      case 'set':
        newBalance = parseFloat(amount);
        break;
    }

    await transaction(async (connection) => {
      // 更新用户余额
      await connection.execute(
        'UPDATE users SET balance = ?, updated_at = NOW() WHERE id = ?',
        [newBalance, userId]
      );

      // 记录余额变更（如果是充值操作）
      if (operation === 'add') {
        await connection.execute(
          `INSERT INTO recharge_records (user_id, amount, payment_method, status, remark, created_at) 
           VALUES (?, ?, 'admin', 'completed', ?, NOW())`,
          [userId, amount, remark || '管理员充值']
        );
      }
    });

    ResponseHelper.success(res, {
      old_balance: formatAmount(currentBalance),
      new_balance: formatAmount(newBalance),
      operation,
      amount: formatAmount(amount)
    }, '用户余额更新成功');

  } catch (error) {
    console.error('更新用户余额错误:', error);
    ResponseHelper.serverError(res, '更新用户余额失败');
  }
});

// 切换用户状态
router.put('/users/:id/status', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const userId = req.params.id;
    const { status } = req.body;

    if (!['active', 'inactive'].includes(status)) {
      return ResponseHelper.error(res, '用户状态无效', 400);
    }

    // 检查用户是否存在
    const users = await query('SELECT id, status FROM users WHERE id = ?', [userId]);
    if (users.length === 0) {
      return ResponseHelper.notFound(res, '用户不存在');
    }

    // 更新用户状态
    await query(
      'UPDATE users SET status = ?, updated_at = NOW() WHERE id = ?',
      [status, userId]
    );

    ResponseHelper.success(res, {
      user_id: userId,
      old_status: users[0].status,
      new_status: status
    }, '用户状态更新成功');

  } catch (error) {
    console.error('切换用户状态错误:', error);
    ResponseHelper.serverError(res, '切换用户状态失败');
  }
});

// 删除用户
router.delete('/users/:id', authenticateToken, requireAdmin, validateId, async (req, res) => {
  try {
    const userId = req.params.id;

    // 检查用户是否存在
    const users = await query('SELECT id, role FROM users WHERE id = ?', [userId]);
    if (users.length === 0) {
      return ResponseHelper.notFound(res, '用户不存在');
    }

    // 不允许删除管理员账户
    if (users[0].role === 'admin') {
      return ResponseHelper.error(res, '不能删除管理员账户', 403);
    }

    // 检查用户是否有未完成的订单
    const activeOrders = await query(
      'SELECT id FROM orders WHERE user_id = ? AND status IN ("pending", "confirmed")',
      [userId]
    );

    if (activeOrders.length > 0) {
      return ResponseHelper.error(res, '用户有未完成的订单，无法删除', 400);
    }

    // 删除用户
    await query('DELETE FROM users WHERE id = ?', [userId]);

    ResponseHelper.success(res, null, '用户删除成功');

  } catch (error) {
    console.error('删除用户错误:', error);
    ResponseHelper.serverError(res, '删除用户失败');
  }
});

// 获取台球桌列表（管理员）
router.get('/tables', authenticateToken, requireAdmin, validatePagination, async (req, res) => {
  try {
    const { page, pageSize, offset, limit } = calculatePagination(req.query.page, req.query.pageSize);
    const { type, status, search } = req.query;

    // 构建查询条件
    let whereClause = 'WHERE 1=1';
    let queryParams = [];

    if (type) {
      whereClause += ' AND type = ?';
      queryParams.push(type);
    }

    if (status) {
      whereClause += ' AND status = ?';
      queryParams.push(status);
    }

    if (search) {
      whereClause += ' AND (name LIKE ? OR description LIKE ?)';
      const searchPattern = `%${search}%`;
      queryParams.push(searchPattern, searchPattern);
    }

    // 获取台球桌总数
    const countResult = await query(
      `SELECT COUNT(*) as total FROM tables ${whereClause}`,
      queryParams
    );
    const total = countResult[0].total;

    // 获取台球桌列表
    const tables = await query(
      `SELECT id, name, type, hourly_rate, status, description, created_at, updated_at
       FROM tables ${whereClause}
       ORDER BY name
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    ResponseHelper.paginated(res, tables, total, page, pageSize, '获取台球桌列表成功');

  } catch (error) {
    console.error('获取台球桌列表错误:', error);
    ResponseHelper.serverError(res, '获取台球桌列表失败');
  }
});

// 获取订单统计信息
router.get('/orders/stats', authenticateToken, requireAdmin, async (req, res) => {
  try {
    // 获取订单基本统计
    const [orderCounts] = await query(`
      SELECT
        COUNT(*) as total,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
        SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
        SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as today,
        SUM(CASE WHEN status = 'completed' THEN total_cost ELSE 0 END) as totalRevenue,
        SUM(CASE WHEN status = 'completed' AND DATE(created_at) = CURDATE() THEN total_cost ELSE 0 END) as todayRevenue
      FROM orders
    `);

    // 获取订单趋势（最近30天）
    const orderTrend = await query(`
      SELECT
        DATE(created_at) as date,
        COUNT(*) as count,
        SUM(CASE WHEN status = 'completed' THEN total_cost ELSE 0 END) as revenue
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
      GROUP BY DATE(created_at)
      ORDER BY date
    `);

    // 获取状态分布统计
    const statusStats = await query(`
      SELECT
        status,
        COUNT(*) as count
      FROM orders
      GROUP BY status
    `);

    ResponseHelper.success(res, {
      ...orderCounts,
      orderTrend,
      statusStats: statusStats.reduce((acc, item) => {
        acc[item.status] = item.count;
        return acc;
      }, {})
    }, '获取订单统计成功');

  } catch (error) {
    console.error('获取订单统计错误:', error);
    ResponseHelper.serverError(res, '获取订单统计失败');
  }
});

// 获取台球桌统计信息
router.get('/tables/stats', authenticateToken, requireAdmin, async (req, res) => {
  try {
    // 获取台球桌基本统计
    const [tableCounts] = await query(`
      SELECT
        COUNT(*) as totalTables,
        SUM(CASE WHEN status = 'available' THEN 1 ELSE 0 END) as availableTables,
        SUM(CASE WHEN status = 'occupied' THEN 1 ELSE 0 END) as occupiedTables,
        SUM(CASE WHEN status = 'maintenance' THEN 1 ELSE 0 END) as maintenanceTables
      FROM tables
    `);

    // 获取台球桌类型分布
    const typeDistribution = await query(`
      SELECT
        type,
        COUNT(*) as count,
        AVG(hourly_rate) as avg_rate
      FROM tables
      GROUP BY type
    `);

    // 获取台球桌使用率统计（最近30天）
    const usageStats = await query(`
      SELECT
        t.id,
        t.name,
        t.type,
        COUNT(o.id) as order_count,
        SUM(o.duration) as total_hours,
        SUM(o.total_cost) as total_revenue
      FROM tables t
      LEFT JOIN orders o ON t.id = o.table_id
        AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        AND o.status = 'completed'
      GROUP BY t.id, t.name, t.type
      ORDER BY total_revenue DESC
    `);

    ResponseHelper.success(res, {
      ...tableCounts,
      typeDistribution,
      usageStats
    }, '获取台球桌统计成功');

  } catch (error) {
    console.error('获取台球桌统计错误:', error);
    ResponseHelper.serverError(res, '获取台球桌统计失败');
  }
});

// 获取台球桌使用统计
router.get('/tables/usage-stats', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { period = '30' } = req.query; // 默认30天

    // 获取时段使用统计
    const hourlyUsage = await query(`
      SELECT
        HOUR(STR_TO_DATE(start_time, '%H:%i:%s')) as hour,
        COUNT(*) as order_count,
        SUM(duration) as total_hours,
        SUM(total_cost) as total_revenue
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
        AND status = 'completed'
      GROUP BY hour
      ORDER BY hour
    `, [period]);

    // 获取每日使用统计
    const dailyUsage = await query(`
      SELECT
        DATE(created_at) as date,
        COUNT(*) as order_count,
        SUM(duration) as total_hours,
        SUM(total_cost) as total_revenue
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
        AND status = 'completed'
      GROUP BY DATE(created_at)
      ORDER BY date
    `, [period]);

    // 获取台球桌类型使用统计
    const typeUsage = await query(`
      SELECT
        t.type,
        COUNT(o.id) as order_count,
        SUM(o.duration) as total_hours,
        SUM(o.total_cost) as total_revenue,
        AVG(o.duration) as avg_duration
      FROM tables t
      LEFT JOIN orders o ON t.id = o.table_id
        AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
        AND o.status = 'completed'
      GROUP BY t.type
      ORDER BY total_revenue DESC
    `, [period]);

    ResponseHelper.success(res, {
      hourlyUsage,
      dailyUsage,
      typeUsage,
      period: parseInt(period)
    }, '获取台球桌使用统计成功');

  } catch (error) {
    console.error('获取台球桌使用统计错误:', error);
    ResponseHelper.serverError(res, '获取台球桌使用统计失败');
  }
});

module.exports = router;
